package com.gbase8c.dmt.db.oracle;

import com.gbase8c.dmt.db.object.RoleObject;

import com.gbase8c.dmt.model.enums.DbType;
import com.gbase8c.dmt.model.migration.dto.DataSourceDto;
import com.gbase8c.dmt.model.migration.dto.RoleDto;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

import java.util.List;
import java.util.Map;
import java.util.Set;

@Slf4j
public class RoleObjectImpl extends MetaImpl implements RoleObject {

    public RoleObjectImpl(DataSourceDto dataSourceDto) {
        super(dataSourceDto);
    }

    @Override
    public List<String> getNames(Map<String, Object> params) {
        String sql = null;
        if (dataSourceDto.getDbVersion().equals(DbType.DbVersion.Oracle_12c)
                || dataSourceDto.getDbVersion().equals(DbType.DbVersion.Oracle_19c)) {
            sql = "select role from dba_roles where oracle_maintained = 'N'";

        } else {
            sql = "select role from dba_roles where role not in ('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER'," +
                    "'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','OEM_MONITOR','HS_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','JAVA_ADMIN','JAVA_DEPLOY','CTXAPP','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR'," +
                    "'GLOBAL_AQ_USER_ROLE','WM_ADMIN_ROLE','EJBCLIENT','XDBADMIN','AUTHENTICATEDUSER','OLAP_DBA','OEM_ADVISOR','SCHEDULER_ADMIN','XDBWEBSERVICES','OLAP_USER','MGMT_USER','DBFS_ROLE','DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE'," +
                    "'ADM_PARALLEL_EXECUTE_TASK','HS_ADMIN_SELECT_ROLE','HS_ADMIN_EXECUTE_ROLE','JMXSERVER','XDB_SET_INVOKER','XDB_WEBSERVICES_WITH_PUBLIC','XDB_WEBSERVICES_OVER_HTTP','ORDADMIN','OLAP_XS_ADMIN','SPATIAL_CSW_ADMIN','CSW_USR_ROLE','CWM_USER'," +
                    "'SPATIAL_WFS_ADMIN','WFS_USR_ROLE','OLAPI_TRACE_USER','OWB_USER','APEX_ADMINISTRATOR_ROLE','OWB$CLIENT','OWB_DESIGNCENTER_VIEW','PDB_DBA','AUDIT_ADMIN','AUDIT_VIEWER','CAPTURE_ADMIN','CDB_DBA','APPLICATION_TRACE_VIEWER','GSMUSER_ROLE'," +
                    "'PROVISIONER','XS_SESSION_ADMIN','XS_NAMESPACE_ADMIN','XS_CACHE_ADMIN','XS_CONNECT','GSM_POOLADMIN_ROLE','OPTIMIZER_PROCESSING_RATE','DBMS_MDX_INTERNAL','RECOVERY_CATALOG_OWNER_VPD','RECOVERY_CATALOG_USER','EM_EXPRESS_BASIC','EM_EXPRESS_ALL'," +
                    "'SYSUMF_ROLE','GSMADMIN_ROLE','GDS_CATALOG_SELECT','GGSYS_ROLE','GGSYS_ROLE','SODA_APP','DATAPATCH_ROLE','DBJAVASCRIPT','RDFCTX_ADMIN','LBAC_DBA','DV_SECANALYST','DV_REALM_OWNER','DV_MONITOR','DV_ADMIN','DV_OWNER','DV_ACCTMGR','DV_PUBLIC'," +
                    "'DV_PATCH_ADMIN','DV_STREAMS_ADMIN','DV_GOLDENGATE_ADMIN','DV_XSTREAM_ADMIN','DV_GOLDENGATE_REDO_ACCESS','DV_AUDIT_CLEANUP','DV_DATAPUMP_NETWORK_LINK','DV_POLICY_OWNER','DV_REALM_RESOURCE','XDB_WEBSERVICES','TIMESERIES_DEVELOPER','TIMESERIES_DBA'," +
                    "'SNMPAGENT','SALES_HISTORY_ROLE','WKUSER')";
        }
        List<String> roleNames = query(sql, new ColumnListHandler<String>());
        return roleNames;
    }

    @Override
    public RoleDto get(String name, Map<String, Object> params) {
        //SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = ? and table_name = ?
        String privsql = "SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = ?";

        String databaseNameTsql = "SELECT DISTINCT TABLE_NAME from dba_tab_privs WHERE grantee = ? AND TABLE_NAME = GRANTOR";
        String databasePrivGrantorsql = "SELECT DISTINCT GRANTOR from dba_tab_privs WHERE grantee = ? AND TABLE_NAME = ?";

        String schemaNameTsql = "SELECT DISTINCT OWNER from dba_tab_privs WHERE grantee = ? AND TABLE_NAME <> GRANTOR";
        String tableNameTsql = "SELECT DISTINCT TABLE_NAME from dba_tab_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME <> GRANTOR";
        String tablePrivGrantorsql = "SELECT DISTINCT GRANTOR from dba_tab_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ?";

        String schemaNameCsql = "SELECT DISTINCT OWNER from dba_col_privs WHERE grantee = ?";
        String tableNameCsql = "SELECT DISTINCT TABLE_NAME from dba_col_privs WHERE grantee = ? AND OWNER = ?";
        String columnNameCsql = "SELECT DISTINCT COLUMN_NAME from dba_col_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ?";
        String columnPrivGrantorsql = "SELECT DISTINCT GRANTOR from dba_col_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME  = ?";

        String databasePrivsql = "SELECT PRIVILEGE from dba_tab_privs WHERE grantee = ? AND TABLE_NAME = ?";
        String tablePrivssql = "SELECT PRIVILEGE from dba_tab_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME = ?";
        //String tablePrivsql = "select TABLE_NAME, Table_priv AS privileges from mysql.tables_priv where user = ?";
        String columnPrivsql = "SELECT PRIVILEGE from dba_col_privs WHERE grantee = ? AND OWNER = ? AND TABLE_NAME =? AND COLUMN_NAME=?";

        RoleDto roleDto = RoleDto.builder().build();
        RoleDto.SysPrivDto sysPrivDto = new RoleDto.SysPrivDto();
        List<RoleDto.DatabasePrivDto> databasePrivDtos = Lists.newArrayList();
        List<RoleDto.TablePrivDto> tablePrivDtos = Lists.newArrayList();
        List<RoleDto.ColumnPrivDto> columnPrivDtos = Lists.newArrayList();
        List<Set<String>> allNames = Lists.newArrayList();
        Set<String> allSchemaNames = Sets.newHashSet();
        Set<String> allTableNames = Sets.newHashSet();
        Set<String> allColumnNames = Sets.newHashSet();

            QueryRunner qr = new QueryRunner(dataSource());
            BeanProcessor beanProcessor = new GenerousBeanProcessor();
            RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
            List<String> sysPriv = null;
            sysPriv = query(privsql, new ColumnListHandler<String>(), name);
            sysPrivilege2String(null,sysPrivDto,sysPriv);

            //库级权限
            List<String> databaseNamesT = query(databaseNameTsql, new ColumnListHandler<String>(), name);
            if (!databaseNamesT.isEmpty() && databaseNamesT != null){
                for (String databaseName:databaseNamesT){
                    allSchemaNames.add(databaseName);
                    RoleDto.DatabasePrivDto databasePrivDto = new RoleDto.DatabasePrivDto();
                    List<String> databasePrivs = query(databasePrivsql, new ColumnListHandler<String>(), name,databaseName);
                    List<String> databaseGrantor = query(databasePrivGrantorsql, new ColumnListHandler<String>(), name,databaseName);
                    databasePrivDto.setSchemaName(databaseName);
                    databasePrivDto.setGrantor(databaseGrantor.get(0));
                    databasePrivDto.setGrantee(name);
                    //库级权限赋值
                    databasePrivilege2String(null,databasePrivDto,databasePrivs);
                    databasePrivDtos.add(databasePrivDto);
                }
            }

            //表级权限
            List<String> schemaNamesT = query(schemaNameTsql, new ColumnListHandler<String>(), name);
            if (!schemaNamesT.isEmpty() && schemaNamesT != null){
                for (String schemaName:schemaNamesT){
                    allSchemaNames.add(schemaName);
                    List<String> tabaleNames = query(tableNameTsql, new ColumnListHandler<String>(), name,schemaName);
                    for (String tableName:tabaleNames){
                        allTableNames.add(tableName);
                        RoleDto.TablePrivDto tablePrivDto = new RoleDto.TablePrivDto();
                        List<String> tablePrivs = query(tablePrivssql, new ColumnListHandler<String>(), name,schemaName,tableName);
                        List<String> tableGrantor = query(tablePrivGrantorsql, new ColumnListHandler<String>(), name,schemaName,tableName);
                        tablePrivDto.setSchemaName(schemaName);
                        tablePrivDto.setTableName(tableName);
                        tablePrivDto.setGrantor(tableGrantor.get(0));
                        tablePrivDto.setGrantee(name);
                        //表级权限赋值
                        tablePrivilege2String(null,tablePrivDto,tablePrivs);
                        tablePrivDtos.add(tablePrivDto);
                    }
                }
            }


            //列级权限
            List<String> schemaNamesC = query(schemaNameCsql, new ColumnListHandler<String>(), name);
            for (String schemaName:schemaNamesC){
                allSchemaNames.add(schemaName);
                List<String> tabaleNames = query(tableNameCsql, new ColumnListHandler<String>(), name,schemaName);
                for (String tableName:tabaleNames){
                    allTableNames.add(tableName);
                    List<String> columnNames = query(columnNameCsql, new ColumnListHandler<String>(), name,schemaName,tableName);
                    for (String columnName:columnNames){
                        allColumnNames.add(columnName);
                        RoleDto.ColumnPrivDto columnPrivDto = new RoleDto.ColumnPrivDto();
                        List<String> columnPrivs = query(columnPrivsql, new ColumnListHandler<String>(), name,schemaName,tableName,columnName);
                        List<String> columnPrivGrantor = query(columnPrivGrantorsql, new ColumnListHandler<String>(), name,schemaName,tableName,columnName);
                        columnPrivDto.setSchemaName(schemaName);
                        columnPrivDto.setTableName(tableName);
                        columnPrivDto.setColumnName(columnName);
                        columnPrivDto.setGrantor(columnPrivGrantor.get(0));
                        columnPrivDto.setGrantee(name);
                        columnPrivilege2String(null,columnPrivDto,columnPrivs);
                        columnPrivDtos.add(columnPrivDto);
                    }
                }
            }
            allNames.add(allSchemaNames);
            allNames.add(allTableNames);
            allNames.add(allColumnNames);
            roleDto = RoleDto.builder()
                    .name(name)
                    .sysPrivDto(sysPrivDto)
                    .databasePrivDtos(databasePrivDtos)
                    .tablePrivDtos(tablePrivDtos)
                    .columnPrivDtos(columnPrivDtos)
                    .allNames(allNames)
                    .build();

        return roleDto;
    }

    @Override
    public RoleDto convert(RoleDto roleDto, Map<String, Object> params) {
        return null;
    }

    @Override
    public String sql(RoleDto roleDto, Map<String, Object> params) {
        return null;
    }
}
